using System.Data;
using System.Data.Common;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.CompilerServices;
using Microsoft.EntityFrameworkCore;

namespace Crossborder.Core.Repository
{
    public class Repository<T> : IRepository<T> where T : class, new()
    {
        private readonly CrossborderDbContext _Context;

        public Repository(CrossborderDbContext context)
        {
            _Context = context;
        }

        public List<T> GetList()
        {
            return _Context.Set<T>().ToList();
        }

        public List<T> GetList(Func<T, bool> func)
        {
            return _Context.Set<T>().Where(func).ToList();
        }

        public T? Get(Func<T, bool> func)
        {
            return _Context.Set<T>().FirstOrDefault(func);
        }

        public T Insert(T entity)
        {
            var dbSet = _Context.Set<T>();
            var res = dbSet.Add(entity).Entity;
            _Context.SaveChanges();
            return res;
        }
        public int BatchInsert(IEnumerable<T> entities)
        {
            #region 非生产环境封装
            _Context.AddRange(entities);
            var res = _Context.SaveChanges();
            return res;
            #endregion

            // TODO： 使用事务提交，以实现批量保存
        }

        public T Delete(T entity)
        {
            var dbSet = _Context.Set<T>();
            var res = dbSet.Remove(entity).Entity;
            _Context.SaveChanges();
            return res;
        }

        public T Update(T entity)
        {
            var dbSet = _Context.Set<T>();
            var res = dbSet.Update(entity).Entity;
            _Context.SaveChanges();
            return res;
        }

        #region 异步方法
        public async Task<T?> GetAsync(Expression<Func<T, bool>> func)
        {
            return await _Context.Set<T>().FirstOrDefaultAsync(func);
        }

        public async Task<List<T>> GetListAsync()
        {
            return await _Context.Set<T>().ToListAsync();
        }

        public async Task<List<T>> GetListAsync(Expression<Func<T, bool>> func)
        {
            return await _Context.Set<T>().Where(func).ToListAsync();
        }

        public async Task<T> InsertAsync(T entity)
        {
            var dbSet = _Context.Set<T>();
            var res = (await dbSet.AddAsync(entity)).Entity;
            await _Context.SaveChangesAsync();
            return res;
        }

        public async Task<int> BatchInsertAsync(IEnumerable<T> entities)
        {
            #region 非生产环境封装
            await _Context.AddRangeAsync(entities);
            var res = await _Context.SaveChangesAsync();
            return res;
            #endregion

            // TODO： 使用事务提交，以实现批量保存
        }

        public async Task<T> DeleteAsync(T entity)
        {
            var dbSet = _Context.Set<T>();
            var res = dbSet.Remove(entity).Entity;
            await _Context.SaveChangesAsync();
            return res;
        }

        public async Task<T> UpdateAsync(T entity)
        {
            var dbSet = _Context.Set<T>();
            var res = dbSet.Update(entity).Entity;
            await _Context.SaveChangesAsync();
            return res;
        }

        #region ADO的方式封装（SQL查询 V1.0）
        /// <summary>
        /// 执行查询SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public async Task<List<T>> QuerySqlAsync(string sql, object? parameters = null)
        {
            using var command = _Context.Database.GetDbConnection().CreateCommand();
            command.CommandText = sql;
            command.CommandType = CommandType.Text;
            if (parameters != null)
            {
                foreach (var prop in parameters.GetType().GetProperties())
                {
                    var value = prop.GetValue(parameters) ?? DBNull.Value;
                    var param = command.CreateParameter();
                    param.ParameterName = "@" + prop.Name;
                    param.Value = value;
                    command.Parameters.Add(param);
                }
            }

            if (command.Connection.State != ConnectionState.Open)
            {
                await command.Connection.OpenAsync();
            }

            using var reader = await command.ExecuteReaderAsync();
            var result = new List<T>();
            var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            while (await reader.ReadAsync())
            {
                var obj = new T();
                foreach (var prop in props)
                {
                    if (!reader.HasColumn(prop.Name) || reader[prop.Name] == DBNull.Value)
                        continue;
                    prop.SetValue(obj, reader[prop.Name]);
                }
                result.Add(obj);
            }
            return result;
        }

        /// <summary>
        /// 执行非查询SQL
        /// </summary>
        /// <typeparam name="int"></typeparam>
        public async Task<int> ExecuteSqlAsync(string sql, object? parameters = null)
        {
            using var command = _Context.Database.GetDbConnection().CreateCommand();
            command.CommandText = sql;
            command.CommandType = CommandType.Text;

            if (parameters != null)
            {
                foreach (var prop in parameters.GetType().GetProperties())
                {
                    var value = prop.GetValue(parameters) ?? DBNull.Value;
                    var param = command.CreateParameter();
                    param.ParameterName = "@" + prop.Name;
                    param.Value = value;
                    command.Parameters.Add(param);
                }
            }

            if (command.Connection.State != ConnectionState.Open)
                await command.Connection.OpenAsync();

            return await command.ExecuteNonQueryAsync();
        }
        #endregion

        #region SQL查询 V2.0
        /// <summary>
        /// SQL查询 V2.0
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">条件参数</param>
        /// <param name="isFuzzyQuery">模糊查询（默认：False）</param>
        /// <returns></returns>
        public Task<List<T>> QueryAsync(string sql, object? parameters = null, bool isFuzzyQuery = false)
        {
            var dbSet = _Context.Set<T>();
            if (parameters != null)
            {
                if (!isFuzzyQuery)
                {
                    foreach (var prop in parameters.GetType().GetProperties())
                    {
                        var value = prop.GetValue(parameters) ?? DBNull.Value;
                        if ((value is string str && string.IsNullOrWhiteSpace(str)) || value.Equals(DBNull.Value))
                            continue;
                        sql += $" AND {prop.Name} = '{value}'";
                    }
                }
                else
                {
                    foreach (var prop in parameters.GetType().GetProperties())
                    {
                        var value = prop.GetValue(parameters) ?? DBNull.Value;
                        if ((value is string str && string.IsNullOrWhiteSpace(str)) || value.Equals(DBNull.Value))
                            continue;
                        sql += $" AND {prop.Name} LIKE '%{value}%'";
                    }
                }
            }
            return dbSet.FromSql(FormattableStringFactory.Create(sql)).ToListAsync();
        }

        /// <summary>
        /// SQL分页查询 V2.0
        /// </summary>
        /// <param name="page">页码</param>
        /// <param name="size">查询数量</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">条件参数</param>
        /// <param name="isFuzzyQuery">模糊查询（默认：False）</param>
        /// <returns></returns>
        public Task<List<T>> QueryPageAsync(int page, int size, string sql, object? parameters = null, bool isFuzzyQuery = false)
        {
            // List<SqlParameter> sqlst = new();
            var dbSet = _Context.Set<T>();
            if (parameters != null)
            {
                if (!isFuzzyQuery)
                {
                    foreach (var prop in parameters.GetType().GetProperties())
                    {
                        var value = prop.GetValue(parameters) ?? DBNull.Value;
                        if ((value is string str && string.IsNullOrWhiteSpace(str)) || value.Equals(DBNull.Value))
                            continue;
                        sql += $" AND {prop.Name} = '{value}'";
                    }
                }
                else
                {
                    foreach (var prop in parameters.GetType().GetProperties())
                    {
                        var value = prop.GetValue(parameters) ?? DBNull.Value;
                        if ((value is string str && string.IsNullOrWhiteSpace(str)) || value.Equals(DBNull.Value))
                            continue;
                        sql += $" AND {prop.Name} LIKE '%{value}%'";
                    }
                }
            }

            var querySql = $"SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY Id ASC) row_number, * FROM ({sql}) t) y WHERE row_number BETWEEN '{((page - 1) * size) + 1}' AND '{page * size}'";

            return dbSet.FromSql(FormattableStringFactory.Create(querySql)).ToListAsync();
        }
        #endregion
        #endregion
    }
    
    public static class DataReaderExtensions
    {
        public static bool HasColumn(this DbDataReader reader, string columnName)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            if (reader.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                return true;

            return false;
        }
    }

    public interface IRepository<T> where T : class, new()
    {
        T? Get(Func<T, bool> func);

        List<T> GetList();

        List<T> GetList(Func<T, bool> func);

        T Insert(T entity);

        int BatchInsert(IEnumerable<T> entities);

        T Delete(T entity);

        T Update(T entity);

        #region 异步方法
        Task<T?> GetAsync(Expression<Func<T, bool>> func);

        Task<List<T>> GetListAsync();

        Task<List<T>> GetListAsync(Expression<Func<T, bool>> func);

        Task<T> InsertAsync(T entity);

        Task<int> BatchInsertAsync(IEnumerable<T> entities);

        Task<T> DeleteAsync(T entity);

        Task<T> UpdateAsync(T entity);

        Task<List<T>> QuerySqlAsync(string sql, object? parameters = null);

        Task<int> ExecuteSqlAsync(string sql, object? parameters = null);

        /// <summary>
        /// SQL查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">条件参数</param>
        /// <param name="isFuzzyQuery">模糊查询（默认：False）</param>
        /// <returns></returns>
        Task<List<T>> QueryAsync(string sql, object? parameters = null, bool isFuzzyQuery = false);

        /// <summary>
        /// SQL分页查询
        /// </summary>
        /// <param name="page">页码</param>
        /// <param name="size">查询数量</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">条件参数</param>
        /// <param name="isFuzzyQuery">模糊查询（默认：False）</param>
        /// <returns></returns>
        Task<List<T>> QueryPageAsync(int page, int size, string sql, object? parameters = null, bool isFuzzyQuery = false);
        #endregion
    }
}